#include "mydatabase.h"
#include <QSqlQuery>
#include <QVariant>
#include <QSqlError>
#include <QDateTime>

MyDatabase::MyDatabase(QObject *parent): QObject(parent)
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName("mydatabase.db");
    m_db.open();
    m_clientID=QString("********");
    m_deviceType=QString("********");
    //
    //m_clientID=QString("33D49E7E");
}
//QSqlDatabase MyDatabase::initialDatabase()
//{
//    // 创建 QSqlDatabase 对象并连接到 SQLite 数据库
//    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
//    db.setDatabaseName("mydatabase.db");
//    return db;
//}
//void MyDatabase::closeDatabase(QSqlDatabase db)
//{
//    db.close();
//}
bool MyDatabase::existTable(QSqlDatabase db, QString tableName)
{
    bool result = false;
    QSqlQuery query0(db);
    bool tableExists0 = query0.exec(QString("select name FROM sqlite_master WHERE type='table' AND name='%1';").arg(tableName));  
    if (!tableExists0 || !query0.next())
    {
        result = false;
    }
    else
    {
        result = true;
    }
    query0.finish();
    return result;
}
bool MyDatabase::existColumn(QSqlDatabase db, QString tableName, QString columnName)
{
    QSqlQuery query(db);
    QString sql = QString("PRAGMA table_info(%1);").arg(tableName);
    bool result = query.exec(sql);
    if (!result) {
        query.finish();
        return false;
    }    
    bool columnExists = false;
    while (query.next()) {
        QString name = query.value(1).toString();
        if (name == columnName) {
            columnExists = true;
            break;
        }
    }    
    query.finish();
    return columnExists;
}
void MyDatabase::openDB()
{
    //m_db.open();
}

void MyDatabase::closeDB()
{
    m_db.close();
}
bool MyDatabase::checkOrCreateDatabase()
{
//    QSqlDatabase db = initialDatabase();
//    if(!db.open())
//    {
//        return false;
//    }
    // 执行 SQL 查询，判断数据库中是否存在某个表
    QSqlQuery query0(m_db);
    //bool tableExists0 = query0.exec("select name FROM sqlite_master WHERE type='table' AND name='weightTable';");
    // 如果查询失败或者结果集中没有数据，则表示表不存在，需要创建
    if (!existTable(m_db, "weightTable")) {
        // 创建表
        bool tableCreated = query0.exec("CREATE TABLE weightTable (id  TEXT DEFAULT (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-a' || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6))))"
                                        " PRIMARY KEY, weight NUMERIC, weightUnit varchar(64), createdTime TimeStamp NOT NULL DEFAULT CURRENT_TIMESTAMP, isSend INTEGER DEFAULT 0, lon NUMERIC, lon_o NUMERIC, lonDir varchar(64), lat NUMERIC, lat_o NUMERIC, latDir varchar(64), height NUMERIC, heightUnit  varchar(64), truckNo varchar(1000), angle NUMERIC, mode INTEGER DEFAULT -1, scheduled INTEGER DEFAULT 1);");

        if (!tableCreated) {
            // 创建表失败，返回 false
            query0.finish();
            return false;
        }

//        bool insertData = query0.exec("INSERT INTO weightTable (weight) VALUES ('999999g');");
////        query0.clear();
////        query0.addBindValue(0);
////        query0.addBindValue(999999);
//        //query0.addBindValue(QDateTime::currentDateTime());

//        //bool insertData = query0.exec();
//        if(!insertData)
//        {
//            query0.finish();
//            return false;
//        }
    }
    query0.finish();
    // 执行 SQL 查询，判断数据库中是否存在某个表
    QSqlQuery query1(m_db);
    //bool tableExists1 = query1.exec("select name FROM sqlite_master WHERE type='table' AND name='configTable';");
    // 如果查询失败或者结果集中没有数据，则表示表不存在，需要创建
    if (!existTable(m_db, "configTable")) {
        // 创建表
        bool tableCreated = query1.exec("CREATE TABLE configTable (id INTEGER PRIMARY KEY, pwd varchar(64), dt45COM varchar(64), dt45Baudrate INTEGER, dt45DataBit INTEGER, dt45StopBit INTEGER, dt45Parity INTEGER, "
                                        "upperUpperLimit NUMERIC, upperLimit  NUMERIC, lowerLimit NUMERIC, lowerLowerLimit NUMERIC, unit varchar(32), language INTEGER, ngCount INTEGER, okCount INTEGER, pwdSwitch INTEGER, collectTime INTEGER, axisCount INTEGER, addrs varchar(1000), scales varchar(1000)"
                                        ", COM4g varchar(64), baudrate4g INTEGER, dataBit4g INTEGER, stopBit4g INTEGER, parity4g INTEGER, deviceID varchar(1000), truckNo varchar(1000), zeroWeight varchar(1000), zeroWeight1 NUMERIC, calThreshold  NUMERIC"
                                        ", encoderCOM varchar(64), encoderBaudrate INTEGER, encoderDataBit INTEGER, encoderStopBit INTEGER, encoderParity INTEGER, encoderSlaveId INTEGER, dt45SlaveId INTEGER, encoderChannel INTEGER, encoderFiltering INTEGER, encoderDirection INTEGER, encoderInterval INTEGER, saveInterval INTEGER, pulsePerRev INTEGER, encoderDecimalPlace INTEGER, deviceType varchar(1000), accumulateZero NUMERIC, autoSave INTEGER, saveDays INTEGER, Backlight INTEGER ,screenSaverTime INTEGER);");

        if (!tableCreated) {
            query1.finish();
            // 创建表失败，返回 false
            return false;
        }

        QString cmd1=QString("INSERT INTO configTable (id, pwd, dt45COM, dt45Baudrate, dt45DataBit, dt45StopBit, dt45Parity, upperUpperLimit, upperLimit, lowerLimit, lowerLowerLimit, unit, language, ngCount, okCount, pwdSwitch, collectTime, axisCount, addrs, scales, COM4g, baudrate4g, dataBit4g, stopBit4g, parity4g, deviceID, truckNo, zeroWeight, zeroWeight1, calThreshold, encoderCOM, encoderBaudrate, encoderDataBit, encoderStopBit, encoderParity, encoderSlaveId, dt45SlaveId, encoderChannel, encoderFiltering, encoderDirection, encoderInterval, saveInterval, pulsePerRev, encoderDecimalPlace, deviceType, accumulateZero, autoSave, saveDays, Backlight, screenSaverTime ) "
                               "VALUES "
                               "(1, '123456', 'ttyS2', 38400, 8, 0, 0, 100, 80, 1, 0, 'kg', 0, 0, 0, 1, 500, 1, '1|', '1|1|1|1|1|1', '', 115200, 8, 0, 0, '%1', '123', '0|0|0|0|0|0', 0, 5, '', 115200, 8, 0, 0, 1, 240, 0, 1, 0, 200, 30, 4000, 1, '%2', 0, 1, 3, 5, 60);")
                           .arg(m_clientID)
                           .arg(m_deviceType);
        bool insertPwd = query1.exec(cmd1);
        if(!insertPwd)
        {
            query1.finish();
            return false;
        }
    }
    else{
        if (!existColumn(m_db, "configTable", "Backlight")) 
        {
            QString alterSql = "ALTER TABLE configTable ADD COLUMN Backlight INTEGER DEFAULT 5;";
            bool alterResult = query1.exec(alterSql);
         
            if (!alterResult) {
                query1.finish();
                return false;
            }
        }
        if (!existColumn(m_db, "configTable", "screenSaverTime")) 
        {
            QString alterSql = "ALTER TABLE configTable ADD COLUMN screenSaverTime INTEGER DEFAULT 60;";
            bool alterResult = query1.exec(alterSql);
         
            if (!alterResult) {
                query1.finish();
                return false;
            }
        }
    }
    // 如果查询失败或者结果集中没有数据，则表示表不存在，需要创建
    if (!existTable(m_db, "weigherTable")) {
        // 创建表
        bool tableCreated = query1.exec("CREATE TABLE weigherTable (id INTEGER PRIMARY KEY, addr INTEGER);");

        if (!tableCreated) {
            query1.finish();
            // 创建表失败，返回 false
            return false;
        }

        bool insertWeigher = query1.exec("INSERT INTO weigherTable (id, addr) "
                                     "VALUES "
                                     "(1, 240);");
        if(!insertWeigher)
        {
            query1.finish();
            return false;
        }
    }
    // 如果查询失败或者结果集中没有数据，则表示表不存在，需要创建
    if (!existTable(m_db, "mqttTable")) {
        // 创建表
        bool tableCreated = query1.exec("CREATE TABLE mqttTable (id  INTEGER "
                                        " PRIMARY KEY, hostName varchar(1000), port INTEGER, topic varchar(1000), userName varchar(1000), pwd varchar(1000), isSub INTEGER DEFAULT 2 NOT NULL, isOpen INTEGER DEFAULT 1  NOT NULL, interval INTEGER, clientID varchar(1000), subTopic varchar(1000) );");

        if (!tableCreated) {
            query1.finish();
            // 创建表失败，返回 false
            return false;
        }
        QString cmd =QString("INSERT INTO mqttTable (id, hostName, port, topic, userName, pwd, interval, clientID, subTopic) "
                              "VALUES "
                              "(1, '39.105.156.99',7792,'sensor/%1/weight/up','labirinth','A$b3C', 30, '%1','sensor/%1/weight/down');")
                          .arg(m_clientID);
        bool insertWeigher = query1.exec(cmd);
        if(!insertWeigher)
        {
            query1.finish();
            return false;
        }
    }
    if (!existTable(m_db, "otaTable")) {
        // 创建表
        bool tableCreated = query1.exec("CREATE TABLE otaTable (id  INTEGER "
                                        " PRIMARY KEY, apn varchar(1000), url varchar(1000) );");

        if (!tableCreated) {
            query1.finish();
            // 创建表失败，返回 false
            return false;
        }

        bool insertOTARec = query1.exec("INSERT INTO otaTable (id, apn, url) "
                                         "VALUES "
                                         "(1, 'CMIOT','http://admin-web-service.labirinth.cn');");
        if(!insertOTARec)
        {
            query1.finish();
            return false;
        }
    }
    if (!existTable(m_db, "versionTable")) {
        // 创建表
        bool tableCreated = query1.exec("CREATE TABLE versionTable (id  INTEGER PRIMARY KEY, version varchar(1000), devTime varchar(1000) );");

        if (!tableCreated) {
            query1.finish();
            // 创建表失败，返回 false
            return false;
        }

        bool insertVersionRec = query1.exec("INSERT INTO versionTable (id, version, devTime) "
                                        "VALUES "
                                        "(1, '1.0.0','2024-12-31');");
        if(!insertVersionRec)
        {
            query1.finish();
            return false;
        }
    }
    bool updateVersionRec = query1.exec("UPDATE versionTable "
                                        "SET version = '1.0.0', devTime = '2024-12-31' "
                                        "WHERE id = 1;");
    if(!updateVersionRec)
    {
        query1.finish();
        return false;
    }


    query1.finish();
    // 关闭数据库连接
    //closeDatabase(db);

    // 返回 true，表示数据库存在并且已创建
    return true;
}

//QList<weightdata*> MyDatabase::getWeightData()
//{
//    QList<weightdata*> list;
//    QSqlDatabase db = initialDatabase();
//    if(!db.open())
//    {
//        return list;
//    }

//    if(existTable(db,"weightTable"))
//    {
//        QSqlQuery query(db);
//        query.exec("SELECT id, weight, createdTime FROM weightTable;");
//        while(query.next())
//        {
//            int id = query.value(0).toInt();
//            float weight = query.value(1).toFloat();
//            QDateTime time = query.value(2).toDateTime();

//            weightdata data = weightdata(id,weight,time);
//            list.push_back(&data);
//        }
//    }
//    else
//    {
//        return list;
//    }
//    // 关闭数据库连接
//    // 关闭数据库连接
//    closeDatabase(db);

//    // 返回 true，表示数据库存在并且已创建
//    m_weightdata =list;
//    return list;
//}
//QList<weightdata*> MyDatabase::weightData() const
//{
//    return m_weightdata;
//}
